package com.sky.mapper;

import com.github.pagehelper.Page;
import com.sky.dto.OrdersPageQueryDTO;
import com.sky.dto.SalesTop10ItemDTO;
import com.sky.entity.Orders;
import com.sky.vo.OrderStatisticsVO;
import com.sky.vo.StatusCountVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDateTime;
import java.util.List;

@Mapper
public interface OrdersMapper {
    public int insert(Orders order);

    void update(Orders orders);

    @Select("select * from orders where number = #{orderNumber}")
    Orders selectByOrderNumber(String orderNumber);

    Page<Orders> selectByUserIdStatus(Long userId, Integer status);

    @Select("select * from orders where id = #{id}")
    Orders selectById(@Param("id")Long id);

    Page<Orders> search(OrdersPageQueryDTO dto);

    OrderStatisticsVO statistics();

    @Select("select * from orders where status = #{status} and order_time < #{oneHourBefore}")
    Page<Orders> selectByStatusAndOrderTimeBefore(@Param("status") Integer status, LocalDateTime oneHourBefore);

    /**
     * ifnull的作用是：当第一个参数为null的时候，用第二个参数来代替
     * sum查询的时候，如果结果集一条记录也没有会返回null
     * count查询的时候，如果结果集一条记录也没有会返回0
     * */
    @Select("select ifnull(sum(amount),0) from orders where delivery_time between #{startTime} and #{endTime} and status = #{status}")
    double sumByStatusAndDeliveryTime(LocalDateTime startTime, LocalDateTime endTime, Integer status);

    int countByStatusAndCreateTime(Integer status, LocalDateTime startTime, LocalDateTime endTime);

    List<SalesTop10ItemDTO> saleTop10(LocalDateTime startTime, LocalDateTime endTime);

    @Select("select ifnull(sum(amount),0) from orders where order_time between #{startTime} and #{endTime} and status = #{status}")
    Double sumByStatusAndCreateTime(Integer status, LocalDateTime startTime, LocalDateTime endTime);


    /**
     *
         SELECT
             IFNULL(MAX(`待接单`),0) `待接单`,
             IFNULL(MAX(`待派送`),0) `待派送`,
             IFNULL(MAX(`已完成`),0) `已完成`,
             IFNULL(MAX(`已取消`),0) `已取消`,
             IFNULL(MAX(`待付款`),0) +
             IFNULL(MAX(`待接单`),0) +
             IFNULL(MAX(`待派送`),0) +
             IFNULL(MAX(`派送中`),0) +
             IFNULL(MAX(`已完成`),0) +
             IFNULL(MAX(`已取消`),0) +
             IFNULL(MAX(`已退款`),0) `总订单数`
         FROM(
             SELECT
                 CASE STATUS WHEN 1 THEN COUNT(*) END AS `待付款`,
                 CASE STATUS WHEN 2 THEN COUNT(*) END AS `待接单`,
                 CASE STATUS WHEN 3 THEN COUNT(*) END AS `待派送`,
                 CASE STATUS WHEN 4 THEN COUNT(*) END AS `派送中`,
                 CASE STATUS WHEN 5 THEN COUNT(*) END AS `已完成`,
                 CASE STATUS WHEN 6 THEN COUNT(*) END AS `已取消`,
                 CASE STATUS WHEN 7 THEN COUNT(*) END AS `已退款`
             FROM orders
             GROUP BY `status`
         )t
     * */
    @Select("SELECT STATUS , COUNT(*) `count` " +
            " FROM orders " +
            " WHERE order_time BETWEEN #{startTime} AND #{endTime} " +
            " GROUP BY `status`" )
    List<StatusCountVO> groupCountByStatusAndCreateTime(LocalDateTime startTime, LocalDateTime endTime);
}
